package orgper

import java.sql.Connection
import java.sql.DriverManager
import java.sql.PreparedStatement
import java.util.concurrent.atomic.AtomicInteger


//数据库地址
def url = "jdbc:oracle:thin:@10.10.66.71:1521:orcl"
//用户名
def username = "dfdev_crux20191224"
//密码
def password = "crux"


Class.forName("oracle.jdbc.driver.OracleDriver")
def conn = DriverManager.getConnection(url, username, password)

/***
 * 步骤：
 * 1.查询用户是否有角色
 * 2.判断用户在角色下是否分配有组织
 * 3.把没有分配组织的用户设取用户的所属组织作为组织权限
 * 4.查询所有的用户有单位的权限，但是没有区划权限的，插入一个同级的区划权限
 */
conn.setAutoCommit(false)

def orgTypeId = getTypeId(conn, "Organization")
def agencyTypeId = getTypeId(conn, "Agency")


//1.获取用户在组织和角色下的所有的数据权限


//处理用户的组织权限
//2.检查用户的所属组织是否已经增加到用户在每个任职组织下的每个角色下
//3.如果没有增加。则增加
//4.检查用户在每个上文组织下的每个角色下的组织权限是否是个单位，
//4.1 如果是单位，则判断单位对应的区划是否已经增加的当前用户的上下文组织下  角色下。（如果没增加，则增加，否则不增加  单位区划权限）
//4.2 如果是区划则不处理


//处理用户的预算单位数据权限

//1.查询所有的单位类型用户
//2. 根据用户的单位找到对应的第一个预算单位
//3. 在用户的每个角色和任职组织下增加 预算单位数据权限
//3.1 判断是否以增加，如果未增加，则增加，否则不增加


//处理用户的业务处室数据权限

//1.查询所有的单位类型用户
//2. 根据用户的单位找到对应的第一个预算单位
//3. 在用户的每个角色和任职组织下增加 预算单位数据权限
//3.1 判断是否以增加，如果未增加，则增加，否则不增加


//1.查询所有的单位用户
List<User> getUnitUser(Connection connection) {
    def ps = connection.prepareStatement("select id,organization,region from usercenter_user where belong_type='ORGUNIT'")
    def rs = ps.executeQuery();
    List<User> userList = []
    while (rs.next()) {
        User user = new User()
        user.id = rs.getLong("id")
        user.region = rs.getLong("region")
        user.organization = rs.getLong("organization")
        user.belongType = "ORGUNIT"

        //根据单位id查询agencyId
        def agencyPs = connection.prepareStatement("select id,name from org_agency where unit_id=:orgId")
        agencyPs.setLong(1, user.organization)
        def agencyRs = agencyPs.executeQuery()
        if (agencyRs.next()) {
            user.agencyId = agencyRs.getLong("id")
            user.agencyName = agencyRs.getString("name")
            userList.add(user)
        }

        agencyRs.close()
        agencyPs.close()
    }
    rs.close()
    ps.close()
}


//3. 在用户的每个角色和任职组织下增加 预算单位数据权限
//3.1 判断是否以增加，如果未增加，则增加，否则不增加
//查找用户的组织权限
//查看用户是否有数据权限，如果没有，则插入一条agency的数据权限
void insertDataPermission(Connection conn,PreparedStatement preparedStatement) {
    List<User> userList = getUnitUser(conn)
    userList.forEach({ user ->
        def resourceId = "id in (" + user.agencyId + ")"
        def resourceName = "资源名称为：" + user.agencyName

        //查找用户组织权限
        def psOrgPa = conn.prepareStatement("select function_group_id,organization from permission_per_assignment where subject_id=:userId and subject_type='USER' and type_id=:orgTypeId")
        psOrgPa.setLong(1, user.id)
        psOrgPa.setLong(2, orgTypeId)
        def rsOrgPa = psOrgPa.executeQuery()
        while (rsOrgPa.next()) {
            //查找用户在当前角色下当前组织下是否有数据权限
            def psDataPa = conn.prepareStatement("select subject_id,function_group_id from permission_per_assignment where subject_id=:userId and subject_type='USER' and function_group_id=:roleId and organization=:orgId and type_id<>:orgTypeId")
            psDataPa.setLong(1, user.id)
            psDataPa.setLong(2, rsOrgPa.getLong("function_group_id"))
            psDataPa.setLong(3, rsOrgPa.getLong("organization"))
            psDataPa.setLong(4, orgTypeId)
            def rsDataPa = psDataPa.executeQuery()
            if (!rsDataPa.next()) {
                //没有数据权限，插入类型为 agency 的数据权限
                def pa = new Pa(id: GenId.generate(), lastModifiedVersion: 0, typeId: agencyTypeId, resourceId: resourceId, resourceName: resourceName, canAccess: 1, subjectId: user.id, subjectType: "USER", permissionRange: "SAME_LEVEL", organization: rsOrgPa.getLong("organization"), functionGroupId: rsOrgPa.getLong("function_group_id"));
                batchInsertIntoPa(preparedStatement, pa)
            }else{
                println("用户${user.id}没有数据权限,不需要处理")
            }
            rsDataPa.close()
            psDataPa.close()
        }
        rsOrgPa.close()
        psOrgPa.close()
    })
}


/***
 * 根据组织orgId返回组织类型
 * @param connection
 * @param orgId
 * @return
 */
String getOrgType(Connection connection, long orgId) {
    def ps = connection.prepareStatement("select id,d_type,region_id from (select id,D_TYPE,region_id from ORG_REGION union select id,D_TYPE,region_id from ORG_BANK union select id,D_TYPE,region_id from ORG_UNIT) where ID=:orgId")
    ps.setLong(1, orgId)
    def rs = ps.executeQuery()
    if (rs.next()) {
        return rs.getString("d_type")
    }
    rs.close()
    ps.close()
    println("组织:${orgId}不存在")
    return null;

}

List<UserPa> getUserPas(Connection connection, long orgTypeId, long agencyTypeId) {
    List<UserPa> userPas = []
    def ps = connection.prepareStatement("select id,subject_id,organization,type_id,function_group_id,resource_id from permission_per_assignment where function_group_id is not null and subject_type=:subjectType")
    ps.setString(1, 'USER')
    def rs = ps.executeQuery()
    def dataPermissionMap = new HashMap<String, List<DataPermission>>()
    def orgPermissionMap = new HashMap<String, List<OrgPermission>>()
    while (rs.next()) {
        //UserPa userPa = new UserPa()

        //userPa.paId = rs.getLong("id")
        def id = rs.getLong("id")
        def userId = rs.getLong("subject_id")
        def orgID = rs.getLong("organization")
        def functionGroupId = rs.getLong("function_group_id")
        def typeId = rs.getLong("type_id")
        def resourceId = rs.getString("resource_id")
        String key = userId + "-" + orgID + "-" + functionGroupId + "-" + typeId
        if (typeId == orgTypeId) {
            def startIdx = resourceId.indexOf("(")
            def endIdx = resourceId.indexOf(")")
            def orgResourceId = resourceId.substring(startIdx + 1, endIdx)

            if (!orgPermissionMap.containsKey(key)) {
                orgPermissionMap.put(key, [])
            }
            OrgPermission orgPermission = new OrgPermission()
            orgPermission.orgId = Long.parseLong(orgResourceId)
            orgPermission.orgType = getOrgType(connection, orgPermission.orgId)
            orgPermissionMap.get(key).add(orgPermission)
        } else {
            def startIdx = resourceId.indexOf("(")
            def endIdx = resourceId.indexOf(")")
            def ids = resourceId.substring(startIdx + 1, endIdx)
            String[] idArr = ids.split(",")
            for (int i = 0; i < idArr.length; i++) {
                if (!dataPermissionMap.containsKey(key)) {
                    dataPermissionMap.put(key, [])
                }
                DataPermission dataPermission = new DataPermission()
                dataPermission.dataId = Long.parseLong(idArr[i])
                dataPermission.typeId = typeId
                dataPermissionMap.get(key).add(dataPermission)
            }
        }

        UserPa userPa = new UserPa();
        userPa.paId = id
        userPa.userId = userId
        userPa.orgId = orgID
        userPa.functionGroupId = functionGroupId
        userPa.dataPermissions = dataPermissionMap

    }
}


long getTypeId(Connection conn, String code) {
    //查询组织类型对应的权限类型ID
    def psorg = conn.prepareStatement("select id from permission_per_type where code=:code")
    psorg.setString(1, code)
    def orgrs = psorg.executeQuery()
    orgrs.next()
    def typeId = orgrs.getLong("id")
    orgrs.close()
    psorg.close()
    return typeId;
}

class UserPa {
    long paId;
    long userId;
    long orgId;
    long functionGroupId;
    List<DataPermission> dataPermissions = []
    List<OrgPermission> orgPermissions = []
}

class OrgPermission {
    long orgId;
    String orgType;
}

class DataPermission {
    long dataId;
    long typeId;
}

class User {
    long id
    String belongType
    long organization
    long region
    long agencyId
    String agencyName
}


//向PA插入数据
def myPaPs = conn.prepareStatement("insert into permission_per_assignment(id,last_modified_version,type_id,resource_id,resource_name,can_access,subject_id,subject_type,permission_range,organization,function_group_id) values(:id,:last_modified_version,:type_id,:resource_id,:resource_name,:can_access,:subject_id,:subject_type,:permission_range,:organization,:function_group_id)")


class Pa {
    long id
    long typeId
    long lastModifiedVersion = 0
    String resourceId
    String resourceName
    boolean canAccess = true
    long subjectId
    String subjectType
    String permissionRange = "SAME_LEVEL"
    long organization
    long functionGroupId
}

void batchInsertIntoPa(PreparedStatement paPs, Pa pa) {
    //id
    paPs.setLong(1, pa.getId())
    //last_modified_version
    paPs.setLong(2, 0);
    //type_id
    paPs.setLong(3, pa.getTypeId());
    //resource_id
    paPs.setString(4, pa.getResourceId());
    //resource_name
    paPs.setString(5, pa.getResourceName());
    //can_access
    paPs.setInt(6, 1);
    //subject_id
    paPs.setLong(7, pa.getSubjectId());
    //subject_type
    paPs.setString(8, "USER");
    //permission_range
    paPs.setString(9, "SAME_LEVEL");
    //organization
    paPs.setLong(10, pa.getOrganization());
    //function_group_id
    paPs.setLong(11, pa.getFunctionGroupId());
    paPs.addBatch()
}

class GenId {
    static AtomicInteger seq = new AtomicInteger(0);
    static long workId = (long) (new Random(System.nanoTime())).nextInt(2048);

    static long generate() {
        return System.currentTimeMillis() << 21 | workId << 10 | (long) seq.getAndUpdate({ operand ->
            ++operand;
            return operand % 1024;
        });
    }
}
